[アップデート]RDS for PostgreSQLがHypoPG拡張機能に対応しました

初めに

昨日先日のアップデートにてRDS for PostgreSQLの拡張機能にHypoPGが追加されました。

HypoPGは実際にテーブルに対してインデックスを貼らず仮想のインデックスを作成する拡張機能となっています。

仮想というと仮想マシンや仮想メモリをイメージされる方も多いですがvirtual indexではなくhypothetical indexであり概念的にインデックスに近い実態があるわけではなく、インデックスがあるかのように振る舞うためのもののようです。

少し調べたのですが執筆時点では日本語の情報が0に等しく、hypotheticalを仮想と呼ぶのが正しいかを判断できないのですが一旦本記事では仮想インデックスと呼称します。

本機能を利用することで実際のインデックス作成に比べCPUやディスク等のリソースを大きく抑えつつ、EXPLAINステートメントを利用した実行計画の確認をすることができます。

なお本拡張機能はあくまでインデックスの作成コストを削減することで検証を迅速に行えるようにするためのものであり、高負荷がかかり続けているような本番環境で負荷をかけずに検証するためのものではないようなのでご注意ください。

現時点でRDS for postgresqlの以下のバージョンが対応しております。

• PostgreSQL 15.3以上
• PostgreSQL 14.8以上
• PostgreSQL 13.11以上

※ 同日追記
執筆後に見直してたところWhat's newでの告知は昨日でしたが、リリース自体は先日の新しいマイナーバージョンが出たタイミングで行われていたようです。

https://aws.amazon.com/jp/about-aws/whats-new/2023/05/amazon-rds-postgresql-supports-new-minor-versions/
With this release, RDS for PostgreSQL adds support for a new extension HypoPG

インストール

お決まりのshared_preload_librariesの設定変更...は不要ですので、そのままCREATE EXTENSIONを実行すれば問題ありません。

postgres=> CREATE EXTENSION hypopg;
CREATE EXTENSION
postgres=> SELECT * from pg_extension ;
  oid  | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+---------+----------+--------------+----------------+------------+-----------+--------------
 14498 | plpgsql |       10 |           11 | f              | 1.0        |           |
 16408 | hypopg  |       10 |         2200 | t              | 1.3.1      |           |
(2 rows)

テストデータの作成

連番と適当なデータを100万件ほど入れたテーブルを作成しておきます。

postgres=> CREATE TABLE history(id serial, text text, create_date timestamp DEFAULT now());
CREATE TABLE
postgres=> INSERT INTO history(text) SELECT md5(generate_series::text) FROM generate_series(1,1000000);
INSERT 0 1000000
postgres=> ANALYZE;
ANALYZE
postgres=> EXPLAIN SELECT * from history WHERE id = 1000;
                                QUERY PLAN
---------------------------------------------------------------------------
 Gather  (cost=1000.00..15554.43 rows=1 width=45)
   Workers Planned: 2
   ->  Parallel Seq Scan on history  (cost=0.00..14554.33 rows=1 width=45)
         Filter: (id = 1000)
(4 rows)

仮想インデックスの作成

hypopg_create_index()にCREATE　INDEX文を渡すとhypo()を実行すると見える領域に仮想インデックスが作成されます。

postgres=> SELECT * FROM hypopg_create_index('CREATE INDEX ON history (id)');
 indexrelid |        indexname
------------+-------------------------
      14495 | <14495>btree_history_id
(1 row)
--作成したテーブルから実際にインデックスがあるように見えるわけではない
postgres=>　\d history
                                         Table "public.history"
   Column    |            Type             | Collation | Nullable |               Default
-------------+-----------------------------+-----------+----------+-------------------------------------
 id          | integer                     |           | not null | nextval('history_id_seq'::regclass)
 text        | text                        |           |          |
 create_date | timestamp without time zone |           |          | now()

postgres=> SELECT * from hypopg();
        indexname        | indexrelid | indrelid | innatts | indisunique | indkey | indcollation | indclass | indoption | indexprs | indpred | amid
-------------------------+------------+----------+---------+-------------+--------+--------------+----------+-----------+----------+---------+------
 <14495>btree_history_id |      14495 |    16455 |       1 | f           | 1      | 0            | 1978     |           |          |         |  403
(1 row)

\timingで実際のインデックス作成と処理速度を比較してみると発行にかかる時間に大きく差があることがわかります。

postgres=> SELECT * FROM hypopg_create_index('CREATE INDEX ON history (id)');
 indexrelid |        indexname
------------+-------------------------
      14495 | <14495>btree_history_id
(1 row)

Time: 1.985 ms
postgres=> CREATE INDEX ON history(id);
CREATE INDEX
Time: 928.165 ms

格納先についてはhypo_index_remove()でメモリを解放していることや、セッション終了時に削除されることからプロセスメモリ領域(work_mem)かどこかに格納されているのではないかと推定しています。

postgres=> SELECT * from hypopg();
        indexname        | indexrelid | indrelid | innatts | indisunique | indkey | indcollation | indclass | indoption | indexprs | indpred | amid
-------------------------+------------+----------+---------+-------------+--------+--------------+----------+-----------+----------+---------+------
 <14495>btree_history_id |      14495 |    16455 |       1 | f           | 1      | 0            | 1978     |           |          |         |  403
(1 row)
postgres=> exit;
sh-4.2$ psql -h xxxxx.ap-northeast-1.rds.amazonaws.com -U postgres
...
postgres=> select * from hypopg();
 indexname | indexrelid | indrelid | innatts | indisunique | indkey | indcollation | indclass | indoption | indexprs | indpred | amid
-----------+------------+----------+---------+-------------+--------+--------------+----------+-----------+----------+---------+------
(0 rows)

仮想インデックスを利用したクエリ

仮想インデックスを利用した実行計画の確認は通常通りEXPLAIN SELECTで実行すれば問題ありません。

postgres=> EXPLAIN SELECT * FROM history WHERE id = 1000;
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Index Scan using "<14495>btree_history_id" on history  (cost=0.05..8.07 rows=1 width=45)
   Index Cond: (id = 1000)
(2 rows)

ただ実際にインデックスが存在するわけではないのでEXPLAIN ANALYZEといった実際に実行される場合にはインデックスは適用されません。

postgres=> EXPLAIN ANALYZE SELECT * FROM history WHERE id = 1000;
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..15554.43 rows=1 width=45) (actual time=130.881..131.184 rows=0 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on history  (cost=0.00..14554.33 rows=1 width=45) (actual time=117.797..117.798 rows=0 loops=3)
         Filter: (id = 1000)
         Rows Removed by Filter: 333333
 Planning Time: 0.042 ms
 Execution Time: 131.207 ms
(8 rows)

あくまで選択肢を絞るまでの利用となり、最終的には実際のインデックスを貼る必要があるものである点は注意しましょう。

仕組みを追ってみる

virtual indexではなくhypothetical indexなので一体どういう原理だろうと思い、少しだけ仕組みを追ってみました。

hypopg_create_index()の定義をざっくり読む限りCREATE_INDEXのSQL文をpg_parse_query()でパースし、hypo_index_store_parsetree()でその結果を保存しているようです。 https://github.com/HypoPG/hypopg/blob/57d832ce7a2937fe7d42b113c7e95dd1f129795b/hypopg_index.c#L1302
https://github.com/HypoPG/hypopg/blob/57d832ce7a2937fe7d42b113c7e95dd1f129795b/hypopg_index.c#L344

ここからはPostgreSQLの内部の部分も関わってくるため自分の知識レベルでは追いきれずコメント等からの推測となってしまうのですがpg_parse_query()のパース結果というのは RawStmtというPostgreSQLの内部で利用する生の構文のデータのようで、HypoPGではこれを保存しているようです。

なのでおそらく実態としてEXPLAINステートメントによる実行計画作成時のみにこの構文情報を仮想的に読み込みそれを前提としたプランニングを行うための拡張機能ではないかと思われます。

AWSの公式ブログでユースケースとして、仮想インデックスによる追加計算があるため本番環境などの負荷がかかっている環境では推奨されておらず検証環境上での利用を推奨しているため、概ね推定しているような形ではないかと信じています。

終わりに

今回は新しくRDS for PostgreSQLで利用可能になったHypoPGを試してみました。

こちらの拡張機能を利用することで、非常に巨大なテーブルに対するインデックスの検証にかかるコストを大きく削減することができます。

実際には今回のようなサンプルの環境では元々インデックスにかかるコストが大きくないので実感は薄いですが例えば１億件あるようなテーブルとかだと明らかに作業効率が変わってきそうです。

ただあくまでインデックスを貼ること自体に対するコストの軽減であり、稼働中のシステムの負荷に影響を与えず検証できるものではないので検証環境等の負荷のかかっていないような環境で利用するものであることは注意しましょう(実際にどの程度負荷に影響が出るかは不明)。

補足: １億件のテーブルでの差

ほぼ同手順で１億件のデータを生成したテーブルで試してみましたがここまでくると作業効率が段違いになりそうです。
また、実際のインデックスと仮想インデックスが両方存在すると仮想インデックスが優先して利用されるようです。

postgres=> SELECT * FROM hypopg_create_index('CREATE INDEX ON history (id)');
 indexrelid |        indexname
------------+-------------------------
      14495 | <14495>btree_history_id
(1 row)

Time: 31.288 ms
postgres=> CREATE INDEX ON history (id);
CREATE INDEX
Time: 273679.287 ms (04:33.679)
postgres=> EXPLAIN SELECT * FROM history WHERE id = 1000;
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Index Scan using "<14495>btree_history_id" on history  (cost=0.07..8.09 rows=1 width=45)
   Index Cond: (id = 1000)
(2 rows)

Time: 46.177 ms